// ============================================================================
// PAUL SCHOBERG
// CS294, Spring 2000
// Dr. Wang
// ----------------------------------------------------------------------------
// Homeowrk 6 - Table Model using JDBC
// ============================================================================

import java.io.*;
import java.sql.*;
import java.util.*;
import javax.swing.*;
import javax.swing.table.*;

class HW6TableModel extends AbstractTableModel {

// ----------------------------------------------------------------------------
// Data members
// ----------------------------------------------------------------------------

	String		SQL,SQLSelect,SQLConstraints,SQLOrder,querySQL;
	Connection	connection;
	Statement	statement,queryStatement;
	ResultSet	resultSet,queryResult;
	Vector		columnTitles,databaseTitles;
	int		currentRow,columnCount,recordCount,databaseColCount;

// ----------------------------------------------------------------------------
// Constructor
// ----------------------------------------------------------------------------

	HW6TableModel() {
		String	URL	="jdbc:oracle:thin:@cheval.cs.csubak.edu:1521:TEST",
			ID	="cs440",
			PW	="student1";
		SQLSelect	="SELECT Company_Name \"Company\",LastName \"Last Name\",FirstName \"First Name\",Phone1 \"Home\",Phone2 \"Work\",EMail \"e-Mail\" FROM AddressBook ";
		SQLConstraints	="WHERE LastName LIKE '%' ";
		SQLOrder	="ORDER BY LastName,FirstName ASC";
		SQL		=SQLSelect+SQLConstraints+SQLOrder;
		currentRow	=0;
		columnCount	=0;
		recordCount	=0;
		try {	DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
			connection=DriverManager.getConnection(URL,ID,PW);
			connection.setAutoCommit(true); 
			statement=connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
			
			resultSet=statement.executeQuery("SELECT * FROM AddressBook WHERE LastName='zzzzzzz'");
			getDatabaseTitles();
			
			resultSet=statement.executeQuery(SQL);
			getColumnTitles();
			resultSet.last();
			recordCount=resultSet.getRow();
			resultSet.first();
		} catch (SQLException e) {
			System.out.println("A connection to the database was not made.\nThis is a fatal error.\nExecution has stopped.\nGo home.");
			System.exit(-1);
		}
	}

// ----------------------------------------------------------------------------
// isCellEditable()
// ----------------------------------------------------------------------------

	public boolean isCellEditable(int row, int col) { return false; }

// ----------------------------------------------------------------------------
// getColumnTitles()
// ----------------------------------------------------------------------------

	void getColumnTitles() {
		try {	ResultSetMetaData meta	=resultSet.getMetaData();
			columnCount		=meta.getColumnCount();
			columnTitles		=new Vector(columnCount);
			for (int i=1;i<=columnCount;i++) {
				columnTitles.add(ScreenIO.expandString(meta.getColumnName(i),15));
			}
		} catch (SQLException e) { e.printStackTrace(); }
	}

// ----------------------------------------------------------------------------
// getRowCount()
// ----------------------------------------------------------------------------

	public int getRowCount() { return recordCount; }

// ----------------------------------------------------------------------------
// getColumnCount()
// ----------------------------------------------------------------------------

	public int getColumnCount() { return columnCount; }

// ----------------------------------------------------------------------------
// getColumnName()
// ----------------------------------------------------------------------------

	public String getColumnName(int col) { return (String) columnTitles.elementAt(col); }
	
// ----------------------------------------------------------------------------
// getDatabaseTitles()
// ----------------------------------------------------------------------------

	void getDatabaseTitles() {
		try {	ResultSetMetaData meta	=resultSet.getMetaData();
			databaseColCount	=meta.getColumnCount();
			databaseTitles		=new Vector(databaseColCount);
			for (int i=1;i<=databaseColCount;i++) {
				databaseTitles.add(meta.getColumnName(i));
			}
		} catch (SQLException e) { e.printStackTrace(); }
	}

// ----------------------------------------------------------------------------
// getDatabaseColumnCount()
// ----------------------------------------------------------------------------

	public int getDatabaseColumnCount() { return databaseColCount; }

// ----------------------------------------------------------------------------
// getDatabaseColumnName()
// ----------------------------------------------------------------------------

	public String getDatabaseColumnName(int col) { return (String) databaseTitles.elementAt(col); }

// ----------------------------------------------------------------------------
// getValueAt()
// ----------------------------------------------------------------------------

	public Object getValueAt(int row,int col) {
		String value="";
		try {	resultSet.relative(row-currentRow);
			currentRow=row;
			value	  =resultSet.getString(col+1);
		} catch (SQLException e) { e.printStackTrace(); }
		return value;
	}

// ----------------------------------------------------------------------------
// setValueAt()
// ----------------------------------------------------------------------------

	public void setValueAt(Object obj,int row,int col) {
		try {	resultSet.relative(row-currentRow);
			currentRow=row;
			resultSet.updateString(col+1,(String)obj);
			resultSet.updateRow();
			connection.commit();
		} catch (SQLException e) { e.printStackTrace(); }
	}

// ----------------------------------------------------------------------------
// addRow()
// ----------------------------------------------------------------------------

	private String keys,values;
	public void addRow(Homework6 HW6) {
		if ((HW6.lastName.getText().compareTo("")!=0) && (HW6.firstName.getText().compareTo("")!=0)) {
			keys	="";
			values	="";
			addValue(HW6.companyName.getText(), 0);
			addValue(HW6.lastName   .getText(), 1);
			addValue(HW6.firstName  .getText(), 2);
			addValue(HW6.officePhone.getText(), 3);
			addValue(HW6.homePhone  .getText(), 4);
			addValue(HW6.pager      .getText(), 5);
			addValue(HW6.fax        .getText(), 6);
			addValue(HW6.street     .getText(), 7);
			addValue(HW6.city       .getText(), 8);
			addValue(HW6.state      .getText(), 9);
			addValue(HW6.zip        .getText(),10);
			addValue(HW6.eMail      .getText(),11);
			addValue(HW6.memo       .getText(),12);
			if (keys.compareTo("")!=0) {
		       		String SQLInsert="INSERT INTO AddressBook ("+keys+") VALUES("+values+")";
				try {	statement.executeUpdate(SQLInsert);
					reQuery(HW6);
				} catch(SQLException e) { HW6.errorBox("There was an error updating the database - Add failed","Database Update Error"); }
			} else { HW6.infoBox("There was no data in the record - It was not added","Blank Record"); }
		} else { HW6.infoBox("You must supply BOTH a first and a last name","Need More Information"); }
	}
	private void addValue(String value,int field) {
		String key=(String)databaseTitles.elementAt(field);
		if (value!=null)
			if (value.compareTo("")!=0) {
				if (keys.compareTo("")!=0) { keys+=","; values+=","; }
				keys  +=key;
				values+="'"+value+"'";
			}
	}

// ----------------------------------------------------------------------------
// updateRow()
// ----------------------------------------------------------------------------

	public void updateRow(Homework6 HW6) {
		String lName=HW6.lastName .getText(); if (lName==null) { lName=""; }
		String fName=HW6.firstName.getText(); if (fName==null) { fName=""; }
		keys="LastName='"+HW6.origLName+"' AND FirstName='"+HW6.origFName+"'";

		values="";
		addUpdate(HW6.companyName.getText(), 0);
		addUpdate(HW6.lastName   .getText(), 1);
		addUpdate(HW6.firstName  .getText(), 2);
		addUpdate(HW6.officePhone.getText(), 3);
		addUpdate(HW6.homePhone  .getText(), 4);
		addUpdate(HW6.pager      .getText(), 5);
		addUpdate(HW6.fax        .getText(), 6);
		addUpdate(HW6.street     .getText(), 7);
		addUpdate(HW6.city       .getText(), 8);
		addUpdate(HW6.state      .getText(), 9);
		addUpdate(HW6.zip        .getText(),10);
		addUpdate(HW6.eMail      .getText(),11);
		addUpdate(HW6.memo       .getText(),12);
		if (lName.compareTo("")!=0 && fName.compareTo("")!=0) {
	       		String SQLUpdate="UPDATE AddressBook SET "+values+" WHERE "+keys;
			try {	statement.executeUpdate(SQLUpdate);
				reQuery(HW6);
			} catch(SQLException e) { HW6.errorBox("There was an error updating the database - Update failed","Database Update Error"); }
		} else { HW6.infoBox("You must supply BOTH a first and a last name","Need More Information"); }
	}
	private void addUpdate(String value,int field) {
		String key=(String)databaseTitles.elementAt(field);
		if (value==null) { value=""; }
		if (values.compareTo("")!=0) { values+=","; }
		values+=key+"='"+value+"'";
	}

// ----------------------------------------------------------------------------
// deleteRow()
// ----------------------------------------------------------------------------

	public void deleteRow(Homework6 HW6) {
		String lName=HW6.lastName .getText(); if (lName==null) { lName=""; }
		String fName=HW6.firstName.getText(); if (fName==null) { fName=""; }
		if (lName.compareTo("")!=0 && fName.compareTo("")!=0) {
			try {	String SQLDelete="DELETE FROM AddressBook WHERE LastName='"+HW6.origLName+"' AND FirstName='"+HW6.origFName+"'";
				statement.executeUpdate(SQLDelete);
				reQuery(HW6);
			} catch(SQLException e) { HW6.errorBox("There was an error updating the database - Delete failed","Database Update Error"); }
		} else { HW6.infoBox("You must supply BOTH a last name and a first name","Need More Information"); }
	}

// ----------------------------------------------------------------------------
// saveData()
// ----------------------------------------------------------------------------

	public void saveData() {
		try {	connection.commit();
		} catch (SQLException e) { e.printStackTrace(); }
	}

// ----------------------------------------------------------------------------
// doSort()
// ----------------------------------------------------------------------------

	private boolean columnSortAsc[];
	public void doSort(int col,Homework6 HW6) {
		if (columnSortAsc==null) {
			columnSortAsc=new boolean[databaseColCount];
			for (int i=0;i<databaseColCount;i++) { columnSortAsc[i]=false; }
		}
		String msg=(String)databaseTitles.elementAt(col);
		SQLOrder="ORDER BY "+msg;
		columnSortAsc[col]=!columnSortAsc[col];
		if (columnSortAsc[col]) { SQLOrder+=" ASC";  msg="Sorted by "+msg+", Ascending";  }
		else                    { SQLOrder+=" DESC"; msg="Sorted by "+msg+", Descending"; }
		reQuery(HW6);
		HW6.status(msg);
	}

// ----------------------------------------------------------------------------
// reQuery()
// ----------------------------------------------------------------------------

	public void reQuery(String name,Homework6 HW6) {
		SQLConstraints="WHERE LastName LIKE '"+name+"%' OR FirstName LIKE '"+name+"%' ";
		reQuery(HW6);
	}
	public void reQuery(Homework6 HW6) {
		try {	SQL	   =SQLSelect+SQLConstraints+SQLOrder;
			SQLOrder   ="ORDER BY LastName,FirstName ASC";
			resultSet  =statement.executeQuery(SQL);
			recordCount=0;
			currentRow =0;
			if (resultSet.next()) {
				resultSet.last();
				recordCount=resultSet.getRow();
				resultSet.first();
			}
			fireTableDataChanged();
			HW6.showNumberOfRecords();
			HW6.clearSelection();
		} catch (SQLException e) { HW6.errorBox("An SQL error occurred","SQL Error"); }
	}
	
// ----------------------------------------------------------------------------
// doSaveLabels()
// ----------------------------------------------------------------------------

	public void doSaveLabels(String fileName,Homework6 HW6) {
		if (fileName.indexOf(".")==-1) { fileName+=".lbl"; }
		try {	FileWriter fileWriter=new FileWriter(fileName);
			try {	querySQL	="SELECT * FROM AddressBook "+SQLConstraints+SQLOrder;
				queryStatement	=connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
				queryResult	=queryStatement.executeQuery(querySQL);
				String  eol	="\r\n";
				try {	while (queryResult.next()) {
						String	fName	=queryResult.getString( 3); if (fName ==null) { fName ="[no first name]"; }
						String	lName	=queryResult.getString( 2); if (lName ==null) { lName ="[no last name]";  }
						String	street	=queryResult.getString( 8); if (street==null) { street="[no address]";    }
						String	city	=queryResult.getString( 9); if (city  ==null) { city  ="[no city]";       }
						String	state	=queryResult.getString(10); if (state ==null) { state ="[no state]";      }
						String	zip	=queryResult.getString(11); if (zip   ==null) { zip   ="[no zip]";        }
						fileWriter.write(eol);
						fileWriter.write(fName+" "+lName+eol);
						fileWriter.write(street+eol);
						fileWriter.write(city+", "+state+"  "+zip+eol);
						fileWriter.write(eol);
						fileWriter.write(eol);
					}
					HW6.infoBox("The labels have been generated","Save Labels");
				} catch(IOException e) { HW6.errorBox("There was an error writing the labels","Save Labels Error"); }
			} catch (SQLException e) { HW6.errorBox("An SQL error occurred while processing the labels","SQL Error"); }
			fileWriter.flush();
			fileWriter.close();
		} catch(IOException e) { HW6.errorBox("Unable to save to "+fileName,"File Save Error"); }
	}

// ----------------------------------------------------------------------------
// doSaveFields()
// ----------------------------------------------------------------------------

	public void doSaveFields(String fileName,boolean select[],Homework6 HW6) {
		if (fileName.indexOf(".")==-1) { fileName+=".fdf"; }
		try {	FileWriter fileWriter=new FileWriter(fileName);
			try {	querySQL	="SELECT * FROM AddressBook "+SQLConstraints+SQLOrder;
				queryStatement	=connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
				queryResult	=queryStatement.executeQuery(querySQL);
				String  eol	="\r\n",
					value[]	=new String[databaseColCount];
				try {	while (queryResult.next()) {
						fileWriter.write("--------------------"+eol);
						for (int i=0;i<databaseColCount;i++)
							if (select[i]) {
								value[i]=queryResult.getString(i+1);
								if (value[i]==null) { value[i]="[no data]"; }
								fileWriter.write(ScreenIO.expandString(getDatabaseColumnName(i),20)+value[i]+eol);
							}
					}
					HW6.infoBox("The field data text file has been generated","Save Fields");
				} catch(IOException e) { HW6.errorBox("There was an error writing the records","Save Fields Error"); }
			} catch (SQLException e) { HW6.errorBox("An SQL error occurred while processing the records","SQL Error"); }
			fileWriter.flush();
			fileWriter.close();
		} catch(IOException e) { HW6.errorBox("Unable to save to "+fileName,"File Save Error"); }
	}

// ----------------------------------------------------------------------------
// getRowData()
// ----------------------------------------------------------------------------

	public void getRowData(int row,Homework6 HW6) {
		try {	resultSet.relative(row-currentRow);
			currentRow=row;
			querySQL	="SELECT * FROM AddressBook WHERE LastName='"+resultSet.getString(2)+"' AND FirstName='"+resultSet.getString(3)+"'";
			queryStatement	=connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
			queryResult	=queryStatement.executeQuery(querySQL);
			queryResult.first();
			HW6.companyName	.setText(queryResult.getString( 1));
			HW6.lastName	.setText(queryResult.getString( 2));
			HW6.firstName	.setText(queryResult.getString( 3));
			HW6.officePhone	.setText(queryResult.getString( 4));
			HW6.homePhone	.setText(queryResult.getString( 5));
			HW6.pager	.setText(queryResult.getString( 6));
			HW6.fax		.setText(queryResult.getString( 7));
			HW6.street	.setText(queryResult.getString( 8));
			HW6.city	.setText(queryResult.getString( 9));
			HW6.state	.setText(queryResult.getString(10));
			HW6.zip		.setText(queryResult.getString(11));
			HW6.eMail	.setText(queryResult.getString(12));
			HW6.memo	.setText(queryResult.getString(13));
			HW6.recordStatus=HW6.UPDATE_RECORD;
		} catch (Throwable e) { HW6.errorBox("Something really bad happened and what you asked for didn't happen","Really Bad Error"); }
	}

// ----------------------------------------------------------------------------
// pause()
// ----------------------------------------------------------------------------

	public void pause(int time) {
		if (time>0) { try { Thread.sleep(time); } catch(InterruptedException e) { } }
	}
}
